<?php
namespace app\admin\controller;

use think\db\Query;

class Data extends Common {
	
	private $total_length = 0;
	private $tables;
	private $tableNames = array();
	
	public function index(){
		$this->all_tables();
		$this->assign('totalSize',\File::byteFormat($this->total_length));
		$this->assign('countTable',count($this->tables));
		$this->assign('lists',$this->tables);
		return $this->fetch();
	}

	private function all_tables(){
		$this->tables = db()->query("show table status");
		$total_length = 0;
		foreach ($this->tables as $key => $value){
			$this->tableNames[] = $value['Name'];
			$this->tables[$key]['size'] = \File::byteFormat(($value['Data_length'] + $value['Index_length']));
			$total_length += $value['Data_length'] + $value['Index_length'];
		}
		$this->total_length = $total_length;
	}
	
	public function optimize(){
		if (!request()->isAjax()) $this->error('操作失败');
		$this->all_tables();
		if (request()->isPost()){
			$PostTables = $this->request->post('tables');
			$PostTables = rtrim($PostTables,'|');
			$PostTables = explode('|', $PostTables);
			$table = '';
			foreach ($PostTables as $key => $value){
				if (in_array($value, $this->tableNames)){
					$value = addslashes($value);
					$table .= "`$value`,";
				}
			}
			$table = rtrim($table,',');
		}else{
			$table = $this->request->param('table');
			if (!in_array($table, $this->tableNames)){
				$this->ajaxReturn(array('code' => 0));
			}
			$table = "`{$table}`";
		}
		if (!empty($table)){
			db()->query("OPTIMIZE TABLE $table");
			$this->ajaxReturn(array('code' => 1,'msg' => '优化表完成'));
		}else{
			$this->ajaxReturn(array('code' => 0));
		}
	}
	
	public function repair(){
		if (!request()->isAjax()) $this->error('操作失败');
		$this->all_tables();
		if (request()->isPost()){
			$PostTables = $this->request->post('tables');
			$PostTables = rtrim($PostTables,'|');
			$PostTables = explode('|', $PostTables);
			$table = '';
			foreach ($PostTables as $key => $value){
				if (in_array($value, $this->tableNames)){
					$value = addslashes($value);
					$table .= "`$value`,";
				}
			}
			$table = rtrim($table,',');
		}else{
			$table = $this->request->param('table');
			if (!in_array($table, $this->tableNames)){
				$this->ajaxReturn(array('code' => 0));
			}
			$table = "`{$table}`";
		}
		if (!empty($table)){
			db()->query("REPAIR TABLE $table");
			$this->ajaxReturn(array('code' => 1,'msg' => '修复表完成'));
		}else{
			$this->ajaxReturn(array('code' => 0));
		}
	}
	
	public function exportsql(){
		$this->all_tables();
		$table = $this->request->param('table');
		if ($table){
			if (!in_array($table, $this->tableNames)){
				$this->error("不存在表" . ' ' . $table);
			}
			download_content(date('Ymd').'_'.$table.'.sql', table_insert_sql($table));
		}else{
			$this->error('操作失败');
		}
	}
	
	public function backuphandle(){
		if (!request()->isAjax()) $this->error('操作失败');
		$this->all_tables();
		$PostTables = $this->request->post('tables');
		$PostTables = rtrim($PostTables,'|');
		$PostTables = explode('|', $PostTables);
		foreach ($PostTables as $key => $value){
			if (!in_array($value, $this->tableNames)){
				unset($PostTables[$key]);
			}
		}
		$path = config('DB_BACKUP_PATH');
		$this->backupDB('手动备份',$PostTables,$path);
	}
	
	/**
	 * 功能：读取数据库表结构信息
	 */
	private function backupTable($table_list){
		//db()->query("SET OPTION SQL_QUOTE_SHOW_CREATE = 1"); //1，表示表名和字段名会用``包着的,0 则不用``
		$outPut = '';
		if (!is_array($table_list) || empty($table_list)) {
			return false;
		}
		$db = db();
		foreach ($table_list as $table) {
			$outPut .= "# 数据库表：{$table} 结构信息\n";
			$outPut .= "DROP TABLE IF EXISTS `{$table}`;\n";
			$tmp = $db->query("SHOW CREATE TABLE {$table}");
			$outPut .= $tmp[0]['Create Table'] . " ;\n\n";
		}
		return $outPut;
	}
	
	/**
	 * 备份数据库
	 * @param string $type
	 * @param array $tables
	 * @param string $path
	 * @return array
	 */
	private function backupDB($type = "系统自动备份", $tables = array(), $path = ''){
		if (empty($path)) {
			$path = ROOT_PATH.'backup'.DS;
		}
		$M = db();
		function_exists('set_time_limit') && set_time_limit(0); //防止备份数据过程超时
		ignore_user_abort(true);
		@ini_set('memory_limit', '-1');
		
		//如果备份文件夹不存在，则自动建立
		if (!is_dir($path)){
			mkdir($path,0777);
		}
		$filename = date('Ymd').'_'. md5(rand(0, 255) . md5(rand(128, 200)) . rand(100, 768));
		$pre =
		"# -----------------------------------------------------------\n" .
		"# " . get_system_info('site_name') . " database backup files\n" .
		"# URL: " . $this->request->domain(). "\n" .
		"# Type: {$type}\n";
		$bdTable = $this->backupTable($tables); //取得表结构信息
		$outPut = "";
		$file_n = 1;
		$backedTable = array();
		foreach ($tables as $table) {
			$backedTable[] = $table;
			$outPut .= "\n\n# 数据库表：{$table} 数据信息\n";
			$tableInfo = $M->query("SHOW TABLE STATUS LIKE '{$table}'");
			$page = ceil($tableInfo[0]['Rows'] / 10000) - 1;
			for ($i = 0; $i <= $page; $i++) {
				$query = $M->query("SELECT * FROM {$table} LIMIT " . ($i * 10000) . ", 10000");
				foreach ($query as $val) {
					$temSql = "";
					$tn = 0;
					$temSql = '';
					foreach ($val as $value) {
						$temSql .= $tn == 0 ? "" : ",";
						$temSql .= ($value == '' && $value != 0) ? "''" : "'{$value}'";
						$tn++;
					}
					$temSql = "INSERT INTO `{$table}` VALUES ({$temSql});\n";
					$sqlNo = "\n# Time: " . date("Y-m-d H:i:s") . "\n" .
							"# -----------------------------------------------------------\n" .
							"# 当前SQL卷标：#{$file_n}\n# -----------------------------------------------------------\n\n\n";
					if ($file_n == 1) {
						$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $tables) . "的结构信息，表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
					} else {
						$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
					}
					if (mb_strlen($pre,'utf-8') + mb_strlen($sqlNo,'utf-8') + mb_strlen($bdTable,'utf-8') + mb_strlen($outPut,'utf-8') + mb_strlen($temSql,'utf-8') > config("SQLFILESIZE")) {
						$file_name = $path.$filename. "_" . $file_n . ".sql";
						$outPut = $file_n == 1 ? $pre . $sqlNo . $bdTable . $outPut : $pre . $sqlNo . $outPut;
						\File::writeFile($file_name, $outPut);
						$bdTable = $outPut = "";
						$backedTable = array();
						$backedTable[] = $table;
						$file_n++;
					}
					$outPut .= $temSql;
				}
			}
		}
		
		if (mb_strlen($bdTable . $outPut,'utf-8') > 0) {
			$sqlNo = "\n# Time: " . date("Y-m-d H:i:s") . "\n" .
					"# -----------------------------------------------------------\n" .
					"# 当前SQL卷标：#{$file_n}\n# -----------------------------------------------------------\n\n\n";
			if ($file_n == 1) {
				$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $tables) . "的结构信息，表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
			} else {
				$sqlNo = "# Description:当前SQL文件包含了表：" . implode("、", $backedTable) . "的数据" . $sqlNo;
			}
			$file_name = $path.$filename. "_" . $file_n . ".sql";
			$outPut = $file_n == 1 ? $pre . $sqlNo . $bdTable . $outPut : $pre . $sqlNo . $outPut;
			\File::writeFile($file_name, $outPut);
			$file_n++;
		}
		
		/*
		$res = array("status" => 1, "info" => "成功备份所选数据库表结构和数据，本次备份共生成了" . ($file_n - 1) .
				"个SQL文件。", "url" => U('Admin/Data/restore'));
		return $res;
		*/
		$this->ajaxReturn(array('code' => 1,'url' => url('restore'),'msg' => '备份完成'));
	}
	
	public function restore(){
		$fileList = \File::eachlist(config('DB_BACKUP_PATH'));
		$lists = array();
		$totalSize= 0;
		foreach ($fileList as $key => $value){
			if ($value['type'] == 'file' && preg_match('/[\w\.]+\.sql/i', $value['file'])){
				$readInfo = \File::readFile($value['path'],2000);
				$content = explode("\n", $readInfo); //以\n换行拆分成数组
				$bk = array();
				$bk['name'] = $value['path'];
				$bk['url'] = substr($content[2], 7);
				$bk['type'] = substr($content[3], 8);
				$bk['description'] = substr($content[4], 14);
				$bk['time'] = substr($content[5], 8);
				$Size = filesize($value['path']);
				$bk['size'] = \File::byteFormat($Size);
				$totalSize += $Size;
				$bk['file'] = $value['file'];
				$bk['prefix'] = substr($value['file'], 0, strrpos($value['file'], '_'));
				$bk['num'] = substr($value['file'], strrpos($value['file'], '_') + 1, strrpos($value['file'], '.') - 1 - strrpos($value['file'], '_'));
				$lists[filemtime($value['path'])][$value['file']] = $bk;
			}
		}
		$this->assign('lists',$lists);
		krsort($lists); //按时间排序
		$useArr = array();
		foreach ($lists as $key => $value){
			ksort($value);
			foreach ($value as $k => $v){
				$useArr[$k] = $v;
			}
		}
		unset($lists);
		$this->assign('totalSize',\File::byteFormat($totalSize));
		$this->assign('countFile',count($useArr));
		$this->assign('lists',$useArr);
		return $this->fetch();
	}
	
	/**
	 * @return array
	 */
	private function getRestoreFiles($filename){
		$_SESSION['cacheRestore']['time'] = time();
		$path = rtrim(config('DB_BACKUP_PATH'),'\\');
		$path = rtrim($path,'/');
		if (!is_dir($path)) $this->error('目录不存在');
		$handle = opendir($path);
		$sqlFiles = array();
		while ($file = readdir($handle)) {
			//获取以$filename为前缀的所有sql文件
			if (preg_match('/[\w\.]+\.sql/i', $file) && preg_match('/' . $filename. '_/i', $file)){
				$sqlFiles[] = $file;
			}
		}
		closedir($handle);
		if (count($sqlFiles) == 0){
			$this->error('错误的请求，不存在对应的SQL文件');
		}
		// die(json_encode(array("status" => 0, "info" => "错误的请求，不存在对应的SQL文件")));
		//将要还原的sql文件按顺序组成数组，防止先导入不带表结构的sql文件
		$files = array();
		foreach ($sqlFiles as $sqlFile) {
			$k = str_replace(".sql", "", str_replace($filename. "_", "", $sqlFile));
			$files[$k] = $sqlFile;
		}
		unset($sqlFiles, $filename);
		ksort($files);
		$_SESSION['cacheRestore']['files'] = $files;
		return $files;
	}
	
	public function zipList(){
		$fileList = \File::eachlist(config('DB_BACKUP_PATH').'Zip');
		$totalSize= 0;
		foreach ($fileList as $key => $value){
			$size = filesize($value['path']);
			$totalSize += $size;
			$fileList[$key]['size'] = \File::byteFormat($size);
			$fileList[$key]['time'] = date('Y-m-d H:i:s',filemtime($value['path']));
		}
		$this->assign('totalSize',\File::byteFormat($totalSize));
		$this->assign('countFile',count($fileList));
		$this->assign('lists',$fileList);
		return $this->fetch();
	}
	
	public function ziphandler(){
		if (!request()->isAjax()) $this->error('操作失败');
		if (request()->isPost()) {
			header('Content-Type:application/json; charset=utf-8');
			$sqlFiles = explode('|', rtrim($this->request->post('sqlFiles'),'|'));
			if (empty($sqlFiles) || count($sqlFiles) == 0){
				$this->ajaxReturn(array('code' => 0,'msg' => '请选择要打包的sql文件'));
			}
			$files = $sqlFiles;
			$toZip = array();
			foreach ($files as $file) {
				$tem = explode("_", $file);
				unset($tem[count($tem) - 1]);
				$toZip[implode("_", $tem)][] = $file;
			}
			foreach ($toZip as $zipOut => $files) {
				if (\File::zip($files, $zipOut . ".zip", config('DB_BACKUP_PATH') . "Zip".DS,config('DB_BACKUP_PATH'))) {
				} else {
					$this->ajaxReturn(array('code' => 0,'msg' => '打包过程出现错误'));
				}
			}
			$this->ajaxReturn(array('code' => 1,'url' => url('zipList'),'msg' => "打包的sql文件成功，本次打包" . count($toZip) . "个zip文件"));
		}	
	}
	
	public function restoredata(){
		if (!request()->isAjax()) $this->error('操作失败');
		if (request()->isPost()){
			$filename = $this->request->param('file');
			function_exists('set_time_limit') && set_time_limit(0); //防止备份数据过程超时
			@ini_set("memory_limit", '-1');
			//取得需要导入的sql文件
			$files = isset($_SESSION['cacheRestore']) ? $_SESSION['cacheRestore']['files'] : self::getRestoreFiles($filename);
			//取得上次文件导入到sql的句柄位置
			$position = isset($_SESSION['cacheRestore']['position']) ? $_SESSION['cacheRestore']['position'] : 0;
			$M = db();
			$execute = 0;
			$path = config('DB_BACKUP_PATH');
			foreach ($files as $fileKey => $sqlFile) {
				$file = $path. $sqlFile;
				if (!file_exists($file)) continue;
					$file = fopen($file, "rb");
					$sql = "";
					fseek($file, $position); //将文件指针指向上次位置
					while (!feof($file)) {
						$tem = trim(fgets($file));
						//过滤掉空行、以#号注释掉的行、以--注释掉的行
						if (empty($tem) || $tem[0] == '#' || ($tem[0] == '-' && $tem[1] == '-')) continue;
							//统计一行字符串的长度
							$end = (int)(strlen($tem) - 1);
							//检测一行字符串最后有个字符是否是分号，是分号则一条sql语句结束，否则sql还有一部分在下一行中
							if ($tem[$end] == ";") {
								$sql .= $tem;
								// $sql = str_replace("`", "", $sql);
								$M->query($sql);
								$sql = "";
								$execute++;
								if ($execute > 500) {
									$_SESSION['cacheRestore']['position'] = ftell($file);
									$imported = isset($_SESSION['cacheRestore']['imported']) ? $_SESSION['cacheRestore']['imported'] : 0;
									$imported += $execute;
									$_SESSION['cacheRestore']['imported'] = $imported;
									$this->ajaxReturn(array('code' => 1,'msg' => '如果导入SQL文件卷较大(多)导入时间可能需要几分钟甚至更久，请耐心等待导入完成，导入期间请勿刷新本页，当前导入进度：<font color="red">已经导入' . $imported . '条Sql</font>'));
									exit;
								}
							} else {
								$sql .= $tem;
							}
					}
					fclose($file);
					unset($_SESSION['cacheRestore']['files'][$fileKey]);
					$position = 0;
			}
			$time = time() - $_SESSION['cacheRestore']['time'];
			unset($_SESSION['cacheRestore']);
			$this->ajaxReturn(array('code' => 1,'msg' => "导入成功，耗时：{$time} 秒钟"));
		}
	}
	
	/**
	 * 下载文件
	 * header函数
	 */
	public function download(){
		$filename = $this->request->param('file');
		$type = $this->request->param('type');
		if (empty($type)) $type = 'sql';
		if (empty($filename)) $this->error('操作失败');
		set_time_limit(0);  //大文件在读取内容未结束时会被超时处理，导致下载文件不全
		ignore_user_abort(true);
		header('Content-Description: File Transfer');
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment;filename='.$filename);
		header('Content-Transfer-Encoding: binary');
		header('Expires: 0');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Pragma: public');
		if ($type == 'sql'){
			$filepath = config('DB_BACKUP_PATH').$filename;
		}else{
			$filepath = config('DB_BACKUP_PATH').'Zip'.DS.$filename;
		}
		header('Content-Length: ' . filesize($filepath));
		readfile($filepath);
	}
	
	public function removesqlfile(){
		if (!request()->isAjax()) $this->error('操作失败');
		if (request()->isPost()){
			$files = $this->request->post('files');
			$filename = explode('|', rtrim($files,'|'));
		}else{
			$filename = $this->request->param('file');
		}
		if (is_array($filename)){
			foreach ($filename as $Key => $value){
				$filepath = config('DB_BACKUP_PATH').$value;
				if (file_exists($filepath)){
					@unlink($filepath);
				}
			}
		}else{
			$filepath = config('DB_BACKUP_PATH').$filename;
			if (file_exists($filepath)){
				@unlink($filepath);
			}
		}
		$this->ajaxReturn(array('code' => 1,'msg' => '删除完成'));
	}
	
	//解压
	public function unzip(){
		if (!request()->isAjax()) $this->error('操作失败');
		$filename = $this->request->param('file');
		if (empty($filename)) $this->ajaxReturn(array('code' => 0,'msg' => '请选择要解压的zip文件'));
		if (\File::unzip($filename,config('DB_BACKUP_PATH').'Zip'.DS, config('DB_BACKUP_PATH'))){
			$this->ajaxReturn(array('code' => 1,'msg' => '已解压完成'));
		}else{
			$this->ajaxReturn(array('code' => 0,'msg' => '解压失败'));
		}	
	}
	
	public function removezipfile(){
		if (!request()->isAjax()) $this->error('操作失败');
		if (request()->isPost()){
			$files = $this->request->post('files');
			$filename = explode('|', rtrim($files,'|'));
		}else{
			$filename = $this->request->param('file');
		}
		if (is_array($filename)){
			foreach ($filename as $Key => $value){
				$filepath = config('DB_BACKUP_PATH').'Zip'.DS.$value;
				if (file_exists($filepath)){
					@unlink($filepath);
				}
			}
		}else{
			$filepath = config('DB_BACKUP_PATH').'Zip'.DS.$filename;
			if (file_exists($filepath)){
				@unlink($filepath);
			}
		}
		$this->ajaxReturn(array('code' => 1,'msg' => '删除完成'));
	}
	
	private function dirList(){
		$dirLists = array(
			array(
				'title' => '缓存目录',
				'dir' => CACHE_PATH,
			),
			array(
				'title' => '日志目录',
				'dir' => LOG_PATH,
			),
			array(
				'title' => '网站临时文件',
				'dir' => TEMP_PATH,
			)
		);
		return $dirLists;
	}
	
	public function clear(){
		//$lists = \File::eachlist(CACHE_PATH);
		//p(scandir(RUNTIME_PATH));
		$dirLists = $this->dirList();
		foreach ($dirLists as $key => $value){
			if (is_dir($value['dir'])){
				$dirLists[$key]['filesize'] = \File::byteFormat(\File::dirSize($value['dir']));
			}else{
				$dirLists[$key]['filesize'] = '目录不存在';
			}
		}
		$this->assign('lists',$dirLists);
		return $this->fetch();
	}
	
	public function run_clear(){
		if (!request()->isAjax()) $this->error('操作失败');
		$keyArr = explode('|', rtrim(input('post.k'),'|'));
		$dirLists = $this->dirList();
		foreach ($dirLists as $key => $value){
			if (!in_array($key, $keyArr)){
				unset($dirLists[$key]);
			}
		}
		foreach ($dirLists as $key => $value){
			\File::eachDelete($value['dir']);
		}
		$this->ajaxReturn(array('code' => 1,'msg' => '删除完成'));
	}
	
}